package com.example.jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class BookDao {
    @Autowired
    private NamedParameterJdbcTemplate parameterJdbcTemplate;

    public void add() {
        String sql = "INSERT INTO book(id, name, price, author, pubDate) VALUES (:id, :name, :price, :author, :pubDate)";
        Map<String, Object>map = new HashMap<>();
        map.put("id", 11);
        map.put("name", "xiaolu");
        map.put("price", 23.5);
        map.put("author", "lzj");
        map.put("pubDate", new Date());
        parameterJdbcTemplate.update(sql, map);
    }

    /**
     *  冒号后名称和实体属性一致时可用
     */
    public void add2() {
        String sql = "INSERT INTO book(id, name, price, author, pubDate) VALUES (:id, :name, :price, :author, :pubDate)";
        Book book = new Book();
        book.setId(13);
        book.setAuthor("lzj");
        book.setName("西北大妞");
        book.setPubDate(new Date());
        SqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(book);
        parameterJdbcTemplate.update(sql, sqlParameterSource);
    }

    public void get() {
        String sql = "SELECT id, `name`, price, author, pubDate  FROM book WHERE id = :id";
        RowMapper<Book> rowMapper = new BeanPropertyRowMapper<>(Book.class);
        Map<String, Object>map = new HashMap<>();
        map.put("id", 10);
        Book book = parameterJdbcTemplate.queryForObject(sql, map, rowMapper);
        System.out.println(book);
    }

    public void getList() {
        String sql = "SELECT id, `name`, price, author, pubDate  FROM book ";
        RowMapper<Book> rowMapper = new BeanPropertyRowMapper<>(Book.class);
        List<Book> bookList = parameterJdbcTemplate.query(sql, rowMapper);
        System.out.println(bookList);
    }


}




